<?php

namespace App\Exports;

use App\Helpers\ExcelColumn;
use App\Models\Dorm;
use App\Models\DormitoryRating;
use App\Models\DormRatingMain;
use App\Models\Period;
use App\Models\RatingCategory;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class DormitoryScoreExport implements FromArray, WithHeadings, WithTitle, WithCustomStartCell, WithStyles
{
  protected $ratingItemIds = []; //存储所有评分项的id
  protected $itemWidths = [];
  protected $period; //存储当前学期
  protected $ratingScores; //存储每个宿舍的评分
  protected $maxRatingDate;
  protected $row3MergeRange = [];
  protected  $row2 = [''];
  protected  $row3 = ['房号'];
  protected  $row4 = [''];
  public function __construct($period_id)
  {
    $this->period = Period::find($period_id);
    $this->ratingScores = DormitoryRating::query()->where('period_id', $period_id)->get(['dorm_id', 'subitem_id', 'score']);
    $this->maxRatingDate = DormitoryRating::query()->where('period_id', $period_id)->max('rating_date');
    $categories = RatingCategory::with('ratingSubitems')->get();

    $sn = 2;

    $this->row3MergeRange[] = $sn;
    foreach ($categories as $category) {
      $this->row3[] = $category->category_name;
      $itemIndex = 0;
      foreach ($category->ratingSubitems as $subitem) {
        $this->row2[] = '';
        if ($itemIndex > 0) {
          $this->row3[] = '';
        }
        $this->row4[] = $subitem->subitem_name . '（' . $subitem->subitem_score . '分）';
        $this->ratingItemIds[] = $subitem->id;
        $this->itemWidths[] = $subitem->column_width;
        $itemIndex++;
        $sn++;
      }
      $this->row3MergeRange[] = $sn;
    }
    $this->row2[] = $this->maxRatingDate;
    $this->row2[] = '';
    $this->row3[] = '合计';
    $this->row3[] = '备注';
    $this->row4[] = '';
    $this->row4[] = '';
  }

  public function array(): array
  {
    // 这里返回实际的数据内容
    $data = [];
    $groups = $this->ratingScores->groupBy('dorm_id');
    foreach ($groups as $dorm_id => $dorm_scores) {
      $row = [];
      $row[] = Dorm::find($dorm_id)->code; // 这里假设 dorm_id 对应 dormitory 表中的 id
      $getScore = 0;
      foreach ($this->ratingItemIds as $item_id) {
        $score = $dorm_scores->where('subitem_id', $item_id)->first();
        $row[] = $score ? $score->score : 0;
        $getScore += $score ? $score->score : 0;
      }
      $row[] = $getScore;
      $main = DormRatingMain::query()->where('dorm_id', $dorm_id)->where('period_id', $this->period->id)->first();
      $row[] = $main ? $main->remark : '';
      $data[] = $row;
    }
    return $data;
  }

  public function headings(): array
  {
    return [
      ['宿舍安全卫生评分表' . '[' . $this->period->name . ']'],
      $this->row2,
      $this->row3,
      $this->row4
    ];
  }

  public function title(): string
  {
    return $this->period->name;
  }

  public function startCell(): string
  {
    return 'A1';
  }

  public function styles(Worksheet $sheet)
  {
    // $totalColumns = count($this->headings()[3]); //第四行，注意这样取值会导致headings()被二次调用，如果在里面做遇见全局运算处理会导致重复累加
    $totalColumns = count($this->ratingItemIds) + 3; //第四行，注意这样取值会导致headings()被二次调用，如果在里面做遇见全局运算处理会导致重复累加
    $lastLetter = ExcelColumn::numberToLetter($totalColumns);
    $lastLetter_1 = ExcelColumn::numberToLetter($totalColumns - 1);
    // 合并单元格
    $sheet->mergeCells('A1:' . $lastLetter . '1');
    $sheet->mergeCells($lastLetter_1 . '2:' . $lastLetter . '2'); // 合并日期单元格


    $sheet->mergeCells('A3:A4'); // 合并单元格房号
    $sheet->mergeCells($lastLetter_1 . '3:' . $lastLetter_1 . '4'); // 合并合计单元格
    $sheet->mergeCells($lastLetter . '3:' . $lastLetter . '4'); // 合并备注单元格

    // 设置标题样式
    $sheet->getStyle('A1')->getFont()->setBold(true);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal('center');


    // 特别为第四行设置自动换行
    $sheet->getStyle('A3:' . $lastLetter . '4')->getAlignment()->setHorizontal('left');
    $sheet->getStyle('A3:' . $lastLetter . '4')->getAlignment()->setVertical('center');
    $sheet->getStyle('A3:' . $lastLetter . '4')->getAlignment()->setWrapText(true);
    $sheet->getRowDimension(4)->setRowHeight(60);

    // 合并第三行列宽
    foreach ($this->row3MergeRange as $key => $value) {
      if ($key >= count($this->row3MergeRange) - 1) break; // 最后一个不需要合并
      $fromSn = $this->row3MergeRange[$key];
      $toSn    = $this->row3MergeRange[$key + 1] - 1;
      $letterFrom = ExcelColumn::numberToLetter($fromSn);
      $letterTo = ExcelColumn::numberToLetter($toSn);
      $sheet->mergeCells($letterFrom . '3:' . $letterTo . '3'); // 合并项目单元格
    }


    // 设置列宽
    $sheet->getColumnDimension('A')->setWidth(10); // 房号
    $colSn = 1; // 列序号（1开始）
    foreach ($this->itemWidths as $value) {
      $colSn++;
      $l = ExcelColumn::numberToLetter($colSn); //评分项列序号（1开始）
      $sheet->getColumnDimension($l)->setWidth($value);
    }
    $colSn++;
    $l = ExcelColumn::numberToLetter($colSn); //评分项列序号（1开始）
    $sheet->getColumnDimension($l)->setWidth(10);

    $colSn++;
    $l = ExcelColumn::numberToLetter($colSn); //评分项列序号（1开始）
    $sheet->getColumnDimension($l)->setWidth(10);
  }
}
